#!/bin/bash

# 定义变量：数据同步日期，表示同步哪一条日志数据
# 第1、执行shell脚本时，传递参数
# 第2、如果没有传递参数，同步前一天数据
# todo 大数据-电商数仓-07-商品主题商品诊断看板
if [ -n "$1" ] ; then
data_date=$1
else
data_date=`date -d "-1 days" +%F`
fi

# 加载数据语句
DIM_MD_AREAS_SQL="
USE product_order;
INSERT OVERWRITE TABLE product_order.dws_product_traffic_metrics_new PARTITION (dt='${data_date}')
SELECT
    product_id,
    COUNT(DISTINCT CASE WHEN behavior_type = 'click' THEN user_id END) AS total_visits,
    COUNT(DISTINCT CASE WHEN channel = '手淘搜索' AND behavior_type = 'click' THEN user_id END) AS search_visits,
    COUNT(DISTINCT CASE WHEN channel = '聚划算' AND behavior_type = 'click' THEN user_id END) AS jhs_visits,
    COUNT(DISTINCT CASE WHEN channel = '直通车' AND behavior_type = 'click' THEN user_id END) AS direct_visits
FROM product_order.ods_user_action_log
WHERE dt = '${data_date}'
GROUP BY product_id;

INSERT OVERWRITE TABLE product_order.dws_product_conversion_metrics_new PARTITION (dt='${data_date}')
SELECT
    log.product_id,
    ROUND(COUNT(DISTINCT CASE WHEN origin.order_status = '已支付' THEN log.user_id END) * 100.0
              / NULLIF(COUNT(DISTINCT CASE WHEN log.behavior_type = 'click' THEN log.user_id END), 0), 2) AS payment_rate,
    ROUND(COUNT(DISTINCT CASE WHEN log.behavior_type = 'cart' THEN log.user_id END) * 100.0
              / NULLIF(COUNT(DISTINCT CASE WHEN log.behavior_type = 'click' THEN log.user_id END), 0), 2) AS addcart_rate,
    ROUND(COUNT(DISTINCT CASE WHEN log.behavior_type = 'favorite' THEN log.user_id END) * 100.0
              / NULLIF(COUNT(DISTINCT CASE WHEN log.behavior_type = 'click' THEN log.user_id END), 0), 2) AS collect_rate,
    ROUND(COALESCE(SUM(origin.payment_amount)
                       / NULLIF(COUNT(DISTINCT log.user_id), 0), 0), 2) AS avg_customer_value
FROM product_order.ods_user_action_log log
         LEFT JOIN product_order.ods_order_origin origin
                   ON log.user_id = origin.user_id AND log.product_id = origin.product_id AND origin.dt = log.dt
WHERE log.dt = '${data_date}' AND origin.dt = '${data_date}'
GROUP BY log.product_id;

INSERT OVERWRITE TABLE product_order.dws_product_content_metrics_new PARTITION (dt='${data_date}')
SELECT
    log.product_id,
    COUNT(DISTINCT CASE WHEN channel !='其他' AND behavior_type = 'click' THEN origin.user_id END) AS content_visits,
    ROUND(COUNT(DISTINCT CASE WHEN channel !='其他' AND behavior_type = 'favorite' THEN origin.user_id END) * 100.0
              / NULLIF(COUNT(DISTINCT CASE WHEN channel !='其他' AND behavior_type = 'click' THEN origin.user_id END), 0), 2) AS content_collect_rate,
    ROUND(COUNT(DISTINCT CASE WHEN channel !='其他' AND behavior_type = 'cart' THEN origin.user_id END) * 100.0
              / NULLIF(COUNT(DISTINCT CASE WHEN channel !='其他' AND behavior_type = 'click' THEN origin.user_id END), 0), 2) AS content_addcart_rate,
    COALESCE(SUM(origin.payment_amount), 0) AS content_payment,
    COUNT(DISTINCT CASE WHEN channel !='其他' AND origin.order_status = '已支付' THEN origin.user_id END) AS content_buyers
FROM product_order.ods_user_action_log log
         LEFT JOIN product_order.ods_order_origin origin
                   ON log.user_id = origin.user_id AND log.product_id = origin.product_id
WHERE log.dt = '${data_date}' AND origin.dt = '${data_date}' AND log.channel != '其他' AND origin.dt = log.dt
GROUP BY log.product_id;


INSERT OVERWRITE TABLE product_order.dws_product_newbuyer_metrics_new PARTITION (dt='${data_date}')
SELECT
    origin.product_id,
    ROUND(SUM(CASE WHEN log.is_new_buyer = 1 THEN origin.payment_amount ELSE 0 END) * 100.0
              / NULLIF(SUM(origin.payment_amount), 0), 2) AS new_payment_ratio,
    ROUND(COUNT(DISTINCT CASE WHEN log.is_new_buyer = 1 THEN origin.user_id END) * 100.0
              / NULLIF(COUNT(DISTINCT origin.user_id), 0), 2) AS new_buyer_ratio
FROM product_order.ods_order_origin origin
         LEFT JOIN product_order.ods_user_action_log log
                   ON origin.user_id = log.user_id AND origin.product_id = log.product_id AND origin.dt = log.dt
WHERE origin.dt = '${data_date}' AND origin.order_status = '已支付'
GROUP BY origin.product_id;

INSERT OVERWRITE TABLE product_order.dws_product_service_metrics_new PARTITION (dt='${data_date}')
SELECT
    comment.product_id,
    COUNT(CASE WHEN images IS NOT NULL THEN review_id END) AS image_reviews,
    COUNT(CASE WHEN appraise = '1' THEN review_id END) AS positive_reviews,
    ROUND(COUNT(CASE WHEN origin.order_status = '已退款' THEN origin.order_id END) * 100.0
              / NULLIF(COUNT(CASE WHEN origin.order_status != '已退款' THEN origin.order_id END), 0), 2) AS refund_rate
FROM product_order.ods_comment_info comment
         LEFT JOIN product_order.ods_order_origin origin
                   ON comment.order_id = origin.order_id
WHERE comment.dt = '${data_date}'
GROUP BY comment.product_id;


WITH t1 AS
         (
             SELECT
                 v.product_id,
                 p.product_name,
                 nvl(v.total_visits,0),
                 nvl(v.search_visits,0),
                 nvl(v.jhs_visits,0),
                 nvl(v.direct_visits,0),
                 nvl(c.payment_rate,0),
                 nvl(c.addcart_rate,0),
                 nvl(c.avg_customer_value,0),
                 nvl(c.collect_rate,0),
                 nvl(cnt.content_visits,0),
                 nvl(cnt.content_collect_rate,0),
                 nvl(cnt.content_addcart_rate,0),
                 nvl(cnt.content_payment,0),
                 nvl(cnt.content_buyers,0),
                 nvl(n.new_payment_ratio,0),
                 nvl(n.new_buyer_ratio,0),
                 nvl(s.image_reviews,0),
                 nvl(s.positive_reviews,0),
                 nvl(s.refund_rate,0),
                 nvl(ROUND(
                             (LEAST(v.total_visits, 100) * 0.15) +
                             (LEAST(v.search_visits, 100) * 0.10) +
                             (LEAST(v.jhs_visits, 100) * 0.05) +
                             (LEAST(v.direct_visits, 100) * 0.05) +
                             (LEAST(c.payment_rate, 100) * 0.12) +
                             (LEAST(c.addcart_rate, 100) * 0.09) +
                             (LEAST(c.avg_customer_value, 100) * 0.06) +
                             (LEAST(c.collect_rate, 100) * 0.03) +
                             (LEAST(s.image_reviews, 100) * 0.06) +
                             (LEAST(s.positive_reviews, 100) * 0.06) +
                             (LEAST(s.refund_rate, 100) * 0.03) +
                             (LEAST(n.new_payment_ratio, 100) * 0.05) +
                             (LEAST(n.new_buyer_ratio, 100) * 0.05) +
                             (LEAST(cnt.content_visits, 100) * 0.03) +
                             (LEAST(cnt.content_collect_rate, 100) * 0.02) +
                             (LEAST(cnt.content_addcart_rate, 100) * 0.02) +
                             (LEAST(cnt.content_payment, 100) * 0.02) +
                             (LEAST(cnt.content_buyers, 100) * 0.02)
                     , 2),0) AS total_score,
                 c.dt
             FROM product_order.dws_product_traffic_metrics_new v
                      left join product_order.ods_product_info  p ON v.product_id = p.product_id
                      LEFT JOIN product_order.dws_product_conversion_metrics_new c ON v.product_id = c.product_id AND v.dt = c.dt
                      LEFT JOIN product_order.dws_product_content_metrics_new cnt ON v.product_id = cnt.product_id AND v.dt = cnt.dt
                      LEFT JOIN product_order.dws_product_newbuyer_metrics_new n ON v.product_id = n.product_id AND v.dt = n.dt
                      LEFT JOIN product_order.dws_product_service_metrics_new s ON v.product_id = s.product_id AND v.dt = s.dt
             WHERE v.dt = '${data_date}'
         )
INSERT into TABLE product_order.ads_product_comprehensive_metrics_new
SELECT
    *,
    CASE
        WHEN total_score >= 85 THEN 'A'
        WHEN total_score >= 70 THEN 'B'
        WHEN total_score >= 50 THEN 'C'
        ELSE 'D'
        END AS compete_level
FROM t1;
"
# 执行SQL语句
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DIM_MD_AREAS_SQL}"